package com.mine.yyb.modules.sys.dao.impl;

import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import com.mine.yyb.common.utils.exception.MineErrorCode;
import com.mine.yyb.common.utils.exception.MineException;
import com.mine.yyb.common.utils.jdbc.MineBaseDao;
import com.mine.yyb.common.utils.jdbc.MinePagination;
import com.mine.yyb.modules.sys.dao.IOrderDao;
import com.mine.yyb.modules.sys.enums.UserRoleEnum;
import com.mine.yyb.modules.sys.vo.OrderVO;
import com.mine.yyb.modules.sys.vo.UserVO;

@Repository
public class OrderDaoImpl extends MineBaseDao implements IOrderDao
{
    private static Logger logger = LoggerFactory.getLogger(OrderDaoImpl.class);
    
    @Override
    public int save(OrderVO orderVO)
        throws MineException
    {
        try
        {
            logger.info(orderVO.toString());
            
            StringBuffer sql = new StringBuffer();
            sql.append(" insert into " + OrderVO.TABLE_NAME);
            sql.append(
                " (orderID,shopSite,label,rebateID,rebate,innerID,parentRebate,parentInnerID,orderPrice,orderDate,isValid,isRebate,activity,productName,price,count,createDate,updateDate)");
            sql.append(
                " values(:orderID,:shopSite,:label,:rebateID,:rebate,:innerID,:parentRebate,:parentInnerID,:orderPrice,:orderDate,:isValid,:isRebate,:activity,:productName,:price,:count,now(),now())");
            
            logger.info(sql.toString());
            return super.update(sql.toString(), orderVO);
        }
        catch (Exception e)
        {
            throw new MineException(MineErrorCode.ERROR_DB_EXECUTE_FAIL, e);
        }
    }
    
    @Override
    public int update(OrderVO orderVO)
        throws MineException
    {
        try
        {
            logger.info(orderVO.toString());
            
            StringBuffer sql = new StringBuffer();
            sql.append("update " + OrderVO.TABLE_NAME + " ");
            sql.append("set updateDate = now() ");
            if (orderVO.getIsValid() != 0)
            {
                sql.append(", isValid = :isValid ");
            }
            if (orderVO.getIsRebate() != 0)
            {
                sql.append(", isRebate = :isRebate ");
            }
            if (orderVO.getRebate() != 0)
            {
                sql.append(", rebate = :rebate ");
            }
            if (orderVO.getOrderPrice() != 0)
            {
                sql.append(", orderPrice = :orderPrice ");
            }
            if (orderVO.getParentRebate() != 0)
            {
                sql.append(", parentRebate = :parentRebate ");
            }
            if (StringUtils.isNotBlank(orderVO.getParentInnerID()))
            {
                sql.append(", parentInnerID = :parentInnerID ");
            }
            if (orderVO.getIsParentRebate() != 0)
            {
                sql.append(", isParentRebate = :isParentRebate ");
            }
            if (StringUtils.isNotBlank(orderVO.getActivity()))
            {
                sql.append(", activity = :activity ");
            }
            if (StringUtils.isNotBlank(orderVO.getProductName()))
            {
                sql.append(", productName = :productName ");
            }
            if (orderVO.getPrice() != 0)
            {
                sql.append(", price = :price ");
            }
            if (orderVO.getCount() != 0)
            {
                sql.append(", count = :count ");
            }
            sql.append("where 1 = 1 ");
            if (StringUtils.isNotBlank(orderVO.getOrderID()))
            {
                sql.append("and orderID = :orderID ");
            }
            if (StringUtils.isNotBlank(orderVO.getInnerID()))
            {
                sql.append("and innerID = :innerID ");
            }
            if (StringUtils.isNotBlank(orderVO.getOrderDate()))
            {
                sql.append("and DATE_FORMAT(orderDate,'%Y-%m') = :orderDate ");
            }
            
            logger.info(sql.toString());
            return super.update(sql.toString(), orderVO);
        }
        catch (Exception e)
        {
            throw new MineException(MineErrorCode.ERROR_DB_EXECUTE_FAIL, e);
        }
    }
    
    @Override
    public List<OrderVO> queryOrderList(Map<String, Object> paramsMap)
        throws MineException
    {
        try
        {
            logger.info(paramsMap.toString());
            
            StringBuffer sql = new StringBuffer();
            sql.append(
                "select id, orderID, shopSite, label, rebateID, innerID, orderPrice, orderDate, isValid, isRebate ");
            sql.append("from " + OrderVO.TABLE_NAME + " ");
            sql.append("where 1 = 1 ");
            if (paramsMap.get("orderID") != null && StringUtils.isNotBlank(paramsMap.get("orderID").toString()))
            {
                sql.append("and orderID = :orderID ");
            }
            if (paramsMap.get("innerID") != null && StringUtils.isNotBlank(paramsMap.get("innerID").toString()))
            {
                sql.append("and innerID = :innerID ");
            }
            if (paramsMap.get("isValid") != null && StringUtils.isNotBlank(paramsMap.get("isValid").toString()))
            {
                sql.append("and isValid = :isValid ");
            }
            if (paramsMap.get("lastDate") != null && StringUtils.isNotBlank(paramsMap.get("lastDate").toString()))
            {
                sql.append("and orderDate > :lastDate ");
            }
            if (paramsMap.get("shopSite") != null && StringUtils.isNotBlank(paramsMap.get("shopSite").toString()))
            {
                sql.append("and shopSite in(:shopSite) ");
            }
            
            logger.info(sql.toString());
            return super.queryForList(sql.toString(), paramsMap, OrderVO.class);
        }
        catch (Exception e)
        {
            throw new MineException(MineErrorCode.ERROR_DB_EXECUTE_FAIL, e);
        }
    }
    
    @Override
    public List<OrderVO> queryOrderList(OrderVO orderVO, String startDate, String endDate)
        throws MineException
    {
        try
        {
            logger.info(orderVO.toString() + ", startDate=" + startDate + ", endDate=" + endDate);
            
            StringBuffer sql = new StringBuffer();
            sql.append(
                "select id, orderID, shopSite, label, rebateID, rebate, innerID, orderPrice, orderDate, isValid, isRebate,activity, productName, price, count ");
            sql.append("from " + OrderVO.TABLE_NAME + " a ");
            sql.append("where 1 = 1 ");
            if (StringUtils.isNotBlank(orderVO.getInnerID()))
            {
                sql.append("and a.innerID = :innerID ");
            }
            if (StringUtils.isNotBlank(orderVO.getOrderID()))
            {
                sql.append("and a.orderID = :orderID ");
            }
            if (StringUtils.isNotBlank(orderVO.getShopSite()))
            {
                sql.append("and a.shopSite = :shopSite ");
            }
            if (StringUtils.isNotBlank(startDate))
            {
                sql.append("and a.orderDate >= '" + startDate + " 00:00:00' ");
            }
            if (StringUtils.isNotBlank(endDate))
            {
                sql.append("and a.orderDate <= '" + endDate + " 23:59:59' ");
            }
            if (orderVO.getIsRebate() != 0)
            {
                sql.append("and a.isRebate = :isRebate ");
            }
            if (orderVO.getIsValid() != 0)
            {
                sql.append("and a.isValid = :isValid ");
            }
            
            return super.queryForList(sql.toString(), orderVO, OrderVO.class);
        }
        catch (Exception e)
        {
            throw new MineException(MineErrorCode.ERROR_DB_EXECUTE_FAIL, e);
        }
    }
    
    @Override
    public List<OrderVO> queryQtOrderList(OrderVO orderVO, String startDate, String endDate)
        throws MineException
    {
        try
        {
            logger.info(orderVO.toString() + ", startDate=" + startDate + ", endDate=" + endDate);
            
            StringBuffer sql = new StringBuffer();
            sql.append(
                "select id, orderID, shopSite, label, rebateID, rebate, innerID, orderPrice, orderDate, isValid, isRebate ");
            sql.append("from " + OrderVO.TABLE_NAME + " a ");
            sql.append("where 1 = 1 ");
            if (StringUtils.isNotBlank(orderVO.getInnerID()))
            {
                sql.append("and a.innerID = :innerID ");
            }
            if (StringUtils.isNotBlank(orderVO.getOrderID()))
            {
                sql.append("and a.orderID = :orderID ");
            }
            if (StringUtils.isNotBlank(orderVO.getShopSite()))
            {
                sql.append("and a.shopSite = :shopSite ");
            }
            if (StringUtils.isNotBlank(startDate))
            {
                sql.append("and a.orderDate >= '" + startDate + " 00:00:00' ");
            }
            if (StringUtils.isNotBlank(endDate))
            {
                sql.append("and a.orderDate <= '" + endDate + " 23:59:59' ");
            }
            if (orderVO.getIsRebate() != 0)
            {
                sql.append("and a.isRebate = :isRebate ");
            }
            if (orderVO.getIsValid() != 0)
            {
                sql.append("and a.isValid = :isValid ");
            }
            sql.append("and a.shopSite in ('1','2','3','4','5')");
            return super.queryForList(sql.toString(), orderVO, OrderVO.class);
        }
        catch (Exception e)
        {
            throw new MineException(MineErrorCode.ERROR_DB_EXECUTE_FAIL, e);
        }
    }
    
    @Override
    public List<OrderVO> queryOrderListTo(OrderVO orderVO, String startDate, String endDate)
        throws MineException
    {
        try
        {
            logger.info(orderVO.toString() + ", startDate=" + startDate + ", endDate=" + endDate);
            
            StringBuffer sql = new StringBuffer();
            sql.append("select a.id, a.orderID, a.shopSite, a.rebateID, a.rebate, a.innerID, ");
            sql.append("a.orderPrice, a.orderDate, a.isValid, a.isRebate, ");
            sql.append("b.username, b.name ");
            sql.append("from " + OrderVO.TABLE_NAME + " a ");
            sql.append("left join " + UserVO.TABLE_NAME + " b ");
            sql.append("on a.innerID = b.innerID ");
            sql.append("where 1=1 ");
            if (StringUtils.isNotBlank(orderVO.getUsername()))
            {
                sql.append("and b.username = :username ");
            }
            if (StringUtils.isNotBlank(orderVO.getInnerID()))
            {
                sql.append("and a.innerID = :innerID ");
            }
            if (StringUtils.isNotBlank(orderVO.getOrderID()))
            {
                sql.append("and a.orderID = :orderID ");
            }
            if (StringUtils.isNotBlank(orderVO.getShopSite()))
            {
                sql.append("and a.shopSite = :shopSite ");
            }
            if (StringUtils.isNotBlank(startDate))
            {
                sql.append("and a.orderDate >= '" + startDate + " 00:00:00' ");
            }
            if (StringUtils.isNotBlank(endDate))
            {
                sql.append("and a.orderDate <= '" + endDate + " 23:59:59' ");
            }
            if (orderVO.getIsRebate() != 0)
            {
                sql.append("and a.isRebate = :isRebate ");
            }
            if (orderVO.getIsValid() != 0)
            {
                sql.append("and a.isValid = :isValid ");
            }
            sql.append("order by a.orderDate desc");
            
            logger.info("sql:{}", sql.toString());
            
            return super.queryForList(sql.toString(), orderVO, OrderVO.class);
        }
        catch (Exception e)
        {
            throw new MineException(MineErrorCode.ERROR_DB_EXECUTE_FAIL, e);
        }
    }
    
    @Override
    public MinePagination<OrderVO> queryOrderList(OrderVO orderVO, int sEcho, int iDisplayStart, int iDisplayLength,
        String startDate, String endDate)
        throws MineException
    {
        try
        {
            logger.info(orderVO.toString() + ", iDisplayStart=" + iDisplayStart + ", iDisplayLength=" + iDisplayLength);
            
            StringBuilder sql = new StringBuilder();
            sql.append("select a.id, a.orderID, a.shopSite, a.rebateID, a.rebate, a.innerID, ");
            sql.append("a.orderPrice, a.orderDate, a.isValid, a.isRebate, ");
            sql.append("a.activity, a.productName, a.price, a.count ");
            sql.append("from " + OrderVO.TABLE_NAME + " a ");
            sql.append("where 1=1 ");
            if (StringUtils.isNotBlank(orderVO.getInnerID()))
            {
                sql.append("and a.innerID = :innerID ");
            }
            if (StringUtils.isNotBlank(orderVO.getShopSite()))
            {
                sql.append("and a.shopSite = :shopSite ");
            }
            if (StringUtils.isNotBlank(orderVO.getOrderID()))
            {
                sql.append("and a.orderID = :orderID ");
            }
            if (StringUtils.isNotBlank(startDate))
            {
                sql.append("and a.orderDate >= '" + startDate + " 00:00:00' ");
            }
            if (StringUtils.isNotBlank(endDate))
            {
                sql.append("and a.orderDate <= '" + endDate + " 23:59:59' ");
            }
            if (orderVO.getIsRebate() != 0)
            {
                sql.append("and a.isRebate = :isRebate ");
            }
            if (orderVO.getIsValid() != 0)
            {
                sql.append("and a.isValid = :isValid ");
            }
            sql.append("order by a.orderDate desc");
            
            logger.info("sql:{}", sql.toString());
            return super.queryForList(sql.toString(), orderVO, OrderVO.class, sEcho, iDisplayStart, iDisplayLength);
        }
        catch (Exception e)
        {
            throw new MineException(MineErrorCode.ERROR_DB_EXECUTE_FAIL, e);
        }
    }
    
    @Override
    public MinePagination<OrderVO> queryQtOrderList(OrderVO orderVO, int sEcho, int iDisplayStart, int iDisplayLength,
        String startDate, String endDate)
        throws MineException
    {
        try
        {
            logger.info(orderVO.toString() + ", iDisplayStart=" + iDisplayStart + ", iDisplayLength=" + iDisplayLength);
            
            StringBuilder sql = new StringBuilder();
            sql.append("select a.id, a.orderID, a.shopSite, a.rebateID, a.rebate, a.innerID, ");
            sql.append("a.orderPrice, a.orderDate, a.isValid, a.isRebate ");
            sql.append("from " + OrderVO.TABLE_NAME + " a ");
            sql.append("where 1=1 ");
            if (StringUtils.isNotBlank(orderVO.getInnerID()))
            {
                sql.append("and a.innerID = :innerID ");
            }
            if (StringUtils.isNotBlank(orderVO.getShopSite()))
            {
                sql.append("and a.shopSite = :shopSite ");
            }
            if (StringUtils.isNotBlank(orderVO.getOrderID()))
            {
                sql.append("and a.orderID = :orderID ");
            }
            if (StringUtils.isNotBlank(startDate))
            {
                sql.append("and a.orderDate >= '" + startDate + " 00:00:00' ");
            }
            if (StringUtils.isNotBlank(endDate))
            {
                sql.append("and a.orderDate <= '" + endDate + " 23:59:59' ");
            }
            if (orderVO.getIsRebate() != 0)
            {
                sql.append("and a.isRebate = :isRebate ");
            }
            if (orderVO.getIsValid() != 0)
            {
                sql.append("and a.isValid = :isValid ");
            }
            sql.append("and a.shopSite in ('1','2','3','4','5')");
            sql.append("order by a.orderDate desc");
            
            logger.info("sql:{}", sql.toString());
            return super.queryForList(sql.toString(), orderVO, OrderVO.class, sEcho, iDisplayStart, iDisplayLength);
        }
        catch (Exception e)
        {
            throw new MineException(MineErrorCode.ERROR_DB_EXECUTE_FAIL, e);
        }
    }
    
    @Override
    public MinePagination<OrderVO> queryOrderListTo(OrderVO orderVO, int sEcho, int iDisplayStart, int iDisplayLength,
        String startDate, String endDate)
        throws MineException
    {
        try
        {
            logger.info(orderVO.toString() + ", iDisplayStart=" + iDisplayStart + ", iDisplayLength=" + iDisplayLength);
            
            StringBuilder sql = new StringBuilder();
            sql.append("select a.id, a.orderID, a.shopSite, a.rebateID, a.rebate, a.innerID, ");
            sql.append("a.orderPrice, a.orderDate, a.isValid, a.isRebate, ");
            sql.append("b.username, b.name ");
            sql.append("from " + OrderVO.TABLE_NAME + " a ");
            sql.append("left join " + UserVO.TABLE_NAME + " b ");
            sql.append("on a.innerID = b.innerID ");
            sql.append("where 1=1 ");
            if (StringUtils.isNotBlank(orderVO.getUsername()))
            {
                sql.append("and b.username = :username ");
            }
            if (StringUtils.isNotBlank(orderVO.getInnerID()))
            {
                sql.append("and a.innerID = :innerID ");
            }
            if (StringUtils.isNotBlank(orderVO.getOrderID()))
            {
                sql.append("and a.orderID = :orderID ");
            }
            if (StringUtils.isNotBlank(orderVO.getShopSite()))
            {
                sql.append("and a.shopSite = :shopSite ");
            }
            if (StringUtils.isNotBlank(startDate))
            {
                sql.append("and a.orderDate >= '" + startDate + " 00:00:00' ");
            }
            if (StringUtils.isNotBlank(endDate))
            {
                sql.append("and a.orderDate <= '" + endDate + " 23:59:59' ");
            }
            if (orderVO.getIsRebate() != 0)
            {
                sql.append("and a.isRebate = :isRebate ");
            }
            if (orderVO.getIsValid() != 0)
            {
                sql.append("and a.isValid = :isValid ");
            }
            sql.append("order by a.orderDate desc");
            
            logger.info("sql:{}", sql.toString());
            
            return super.queryForList(sql.toString(), orderVO, OrderVO.class, sEcho, iDisplayStart, iDisplayLength);
        }
        catch (Exception e)
        {
            throw new MineException(MineErrorCode.ERROR_DB_EXECUTE_FAIL, e);
        }
    }
    
    @Override
    public MinePagination<OrderVO> queryChildRebateList(OrderVO orderVO, int sEcho, int iDisplayStart,
        int iDisplayLength, String startDate, String endDate)
        throws MineException
    {
        try
        {
            logger.info(orderVO.toString() + ", iDisplayStart=" + iDisplayStart + ", iDisplayLength=" + iDisplayLength);
            
            StringBuilder sql = new StringBuilder();
            sql.append(
                "select a.id, a.orderID, a.shopSite, a.rebateID, a.rebate, a.parentRebate, a.innerID, a.parentInnerID, ");
            sql.append("a.orderPrice, a.orderDate, a.isValid, a.isRebate, a.isParentRebate, ");
            sql.append("b.username, b.name ");
            sql.append("from " + OrderVO.TABLE_NAME + " a ");
            sql.append("left join " + UserVO.TABLE_NAME + " b ");
            sql.append("on a.innerID = b.innerID ");
            sql.append("where 1=1 ");
            sql.append("and a.parentInnerID is not null ");
            if (StringUtils.isNotBlank(orderVO.getUsername()))
            {
                sql.append("and b.username = :username ");
            }
            if (StringUtils.isNotBlank(orderVO.getInnerID()))
            {
                sql.append("and a.innerID = :innerID ");
            }
            if (StringUtils.isNotBlank(orderVO.getOrderID()))
            {
                sql.append("and a.orderID = :orderID ");
            }
            if (StringUtils.isNotBlank(orderVO.getShopSite()))
            {
                sql.append("and a.shopSite = :shopSite ");
            }
            if (StringUtils.isNotBlank(startDate))
            {
                sql.append("and a.orderDate >= '" + startDate + " 00:00:00' ");
            }
            if (StringUtils.isNotBlank(endDate))
            {
                sql.append("and a.orderDate <= '" + endDate + " 23:59:59' ");
            }
            if (orderVO.getIsParentRebate() != 0)
            {
                sql.append("and a.isParentRebate = :isParentRebate ");
            }
            sql.append("order by a.orderDate desc");
            
            logger.info("sql:{}", sql.toString());
            
            return super.queryForList(sql.toString(), orderVO, OrderVO.class, sEcho, iDisplayStart, iDisplayLength);
        }
        catch (Exception e)
        {
            throw new MineException(MineErrorCode.ERROR_DB_EXECUTE_FAIL, e);
        }
    }
    
    @Override
    public OrderVO querySum(Map<String, Object> paramsMap, String startDate, String endDate)
        throws MineException
    {
        try
        {
            logger.info(paramsMap.toString());
            
            StringBuilder sql = new StringBuilder();
            sql.append("select ifnull(sum(a.rebate), 0) as rebate, ");
            sql.append("ifnull(sum(a.orderPrice), 0) as orderPrice ");
            sql.append("from " + OrderVO.TABLE_NAME + " a ");
            sql.append("where 1=1 ");
            if (paramsMap.get("innerID") != null && StringUtils.isNotBlank(paramsMap.get("innerID").toString()))
            {
                sql.append("and a.innerID = :innerID ");
            }
            if (paramsMap.get("orderID") != null && StringUtils.isNotBlank(paramsMap.get("orderID").toString()))
            {
                sql.append("and a.orderID = :orderID ");
            }
            if (paramsMap.get("shopSite") != null && Integer.parseInt(paramsMap.get("shopSite").toString()) != 0)
            {
                sql.append("and a.shopSite = :shopSite ");
            }
            if (StringUtils.isNotBlank(startDate))
            {
                sql.append("and a.orderDate >= '" + startDate + " 00:00:00' ");
            }
            if (StringUtils.isNotBlank(endDate))
            {
                sql.append("and a.orderDate <= '" + endDate + " 23:59:59' ");
            }
            if (paramsMap.get("isRebate") != null && Integer.parseInt(paramsMap.get("isRebate").toString()) != 0)
            {
                sql.append("and a.isRebate = :isRebate ");
            }
            if (paramsMap.get("isValid") != null && Integer.parseInt(paramsMap.get("isValid").toString()) != 0)
            {
                sql.append("and a.isValid = :isValid ");
            }
            sql.append("order by a.orderDate desc");
            
            logger.info("sql:{}", sql.toString());
            
            return super.queryForObject(sql.toString(), paramsMap, OrderVO.class);
        }
        catch (Exception e)
        {
            throw new MineException(MineErrorCode.ERROR_DB_EXECUTE_FAIL, e);
        }
    }
    
    @Override
    public OrderVO queryQtSum(Map<String, Object> paramsMap, String startDate, String endDate)
        throws MineException
    {
        try
        {
            logger.info(paramsMap.toString());
            
            StringBuilder sql = new StringBuilder();
            sql.append("select ifnull(sum(a.rebate), 0) as rebate, ");
            sql.append("ifnull(sum(a.orderPrice), 0) as orderPrice ");
            sql.append("from " + OrderVO.TABLE_NAME + " a ");
            sql.append("where 1=1 ");
            if (paramsMap.get("innerID") != null && StringUtils.isNotBlank(paramsMap.get("innerID").toString()))
            {
                sql.append("and a.innerID = :innerID ");
            }
            if (paramsMap.get("orderID") != null && StringUtils.isNotBlank(paramsMap.get("orderID").toString()))
            {
                sql.append("and a.orderID = :orderID ");
            }
            if (paramsMap.get("shopSite") != null && Integer.parseInt(paramsMap.get("shopSite").toString()) != 0)
            {
                sql.append("and a.shopSite = :shopSite ");
            }
            if (StringUtils.isNotBlank(startDate))
            {
                sql.append("and a.orderDate >= '" + startDate + " 00:00:00' ");
            }
            if (StringUtils.isNotBlank(endDate))
            {
                sql.append("and a.orderDate <= '" + endDate + " 23:59:59' ");
            }
            if (paramsMap.get("isRebate") != null && Integer.parseInt(paramsMap.get("isRebate").toString()) != 0)
            {
                sql.append("and a.isRebate = :isRebate ");
            }
            if (paramsMap.get("isValid") != null && Integer.parseInt(paramsMap.get("isValid").toString()) != 0)
            {
                sql.append("and a.isValid = :isValid ");
            }
            sql.append("and a.shopSite in ('1','2','3','4','5')");
            sql.append("order by a.orderDate desc");
            
            logger.info("sql:{}", sql.toString());
            
            return super.queryForObject(sql.toString(), paramsMap, OrderVO.class);
        }
        catch (Exception e)
        {
            throw new MineException(MineErrorCode.ERROR_DB_EXECUTE_FAIL, e);
        }
    }
    
    @Override
    public OrderVO queryAllSum(Map<String, Object> paramsMap, String startDate, String endDate)
        throws MineException
    {
        try
        {
            logger.info(paramsMap.toString());
            
            StringBuilder sql = new StringBuilder();
            sql.append("select ifnull(sum(a.rebate), 0) as rebate, ");
            sql.append("ifnull(sum(a.orderPrice), 0) as orderPrice ");
            sql.append("from " + OrderVO.TABLE_NAME + " a ");
            sql.append("left join " + UserVO.TABLE_NAME + " b ");
            sql.append("on a.innerID = b.innerID ");
            sql.append("where 1=1 ");
            if (paramsMap.get("username") != null && StringUtils.isNotBlank(paramsMap.get("username").toString()))
            {
                sql.append("and b.username = :username ");
            }
            if (paramsMap.get("orderID") != null && StringUtils.isNotBlank(paramsMap.get("orderID").toString()))
            {
                sql.append("and a.orderID = :orderID ");
            }
            if (paramsMap.get("shopSite") != null && Integer.parseInt(paramsMap.get("shopSite").toString()) != 0)
            {
                sql.append("and a.shopSite = :shopSite ");
            }
            if (StringUtils.isNotBlank(startDate))
            {
                sql.append("and a.orderDate >= '" + startDate + " 00:00:00' ");
            }
            if (StringUtils.isNotBlank(endDate))
            {
                sql.append("and a.orderDate <= '" + endDate + " 23:59:59' ");
            }
            if (paramsMap.get("isRebate") != null && Integer.parseInt(paramsMap.get("isRebate").toString()) != 0)
            {
                sql.append("and a.isRebate = :isRebate ");
            }
            if (paramsMap.get("isValid") != null && Integer.parseInt(paramsMap.get("isValid").toString()) != 0)
            {
                sql.append("and a.isValid = :isValid ");
            }
            sql.append("order by a.orderDate desc");
            
            logger.info("sql:{}", sql.toString());
            
            return super.queryForObject(sql.toString(), paramsMap, OrderVO.class);
        }
        catch (Exception e)
        {
            throw new MineException(MineErrorCode.ERROR_DB_EXECUTE_FAIL, e);
        }
    }
    
    @Override
    public OrderVO queryAllSumChildRebate(Map<String, Object> paramsMap, String startDate, String endDate)
        throws MineException
    {
        try
        {
            logger.info(paramsMap.toString());
            
            StringBuilder sql = new StringBuilder();
            sql.append("select ifnull(sum(a.parentRebate), 0) as parentRebate, ");
            sql.append("ifnull(sum(a.orderPrice), 0) as orderPrice ");
            sql.append("from " + OrderVO.TABLE_NAME + " a ");
            sql.append("left join " + UserVO.TABLE_NAME + " b ");
            sql.append("on a.innerID = b.innerID ");
            sql.append("where 1=1 ");
            sql.append("and a.parentInnerID is not null ");
            if (paramsMap.get("username") != null && StringUtils.isNotBlank(paramsMap.get("username").toString()))
            {
                sql.append("and b.username = :username ");
            }
            if (paramsMap.get("orderID") != null && StringUtils.isNotBlank(paramsMap.get("orderID").toString()))
            {
                sql.append("and a.orderID = :orderID ");
            }
            if (paramsMap.get("shopSite") != null && Integer.parseInt(paramsMap.get("shopSite").toString()) != 0)
            {
                sql.append("and a.shopSite = :shopSite ");
            }
            if (StringUtils.isNotBlank(startDate))
            {
                sql.append("and a.orderDate >= '" + startDate + " 00:00:00' ");
            }
            if (StringUtils.isNotBlank(endDate))
            {
                sql.append("and a.orderDate <= '" + endDate + " 23:59:59' ");
            }
            if (paramsMap.get("isParentRebate") != null
                && Integer.parseInt(paramsMap.get("isParentRebate").toString()) != 0)
            {
                sql.append("and a.isParentRebate = :isParentRebate ");
            }
            sql.append("order by a.orderDate desc");
            
            logger.info("sql:{}", sql.toString());
            
            return super.queryForObject(sql.toString(), paramsMap, OrderVO.class);
        }
        catch (Exception e)
        {
            throw new MineException(MineErrorCode.ERROR_DB_EXECUTE_FAIL, e);
        }
    }
    
    @Override
    public MinePagination<Map<String, String>> queryRebate(Map<String, Object> paramsMap, int sEcho, int iDisplayStart,
        int iDisplayLength)
        throws MineException
    {
        try
        {
            logger.info(paramsMap.toString());
            
            StringBuilder sql = new StringBuilder();
            sql.append("select DATE_FORMAT(orderDate,'%Y-%m') as orderDate, sum(rebate) as rebate, ");
            sql.append("count(id) as count, sum(isRebate) as isRebate ");
            sql.append("from t_yyb_sys_order ");
            sql.append("where 1 =1 ");
            if (paramsMap.get("innerID") != null && StringUtils.isNotBlank(paramsMap.get("innerID").toString()))
            {
                sql.append("and innerID = :innerID ");
            }
            if (paramsMap.get("orderYear") != null && StringUtils.isNotBlank(paramsMap.get("orderYear").toString()))
            {
                sql.append("and DATE_FORMAT(orderDate,'%Y') = :orderYear ");
            }
            if (paramsMap.get("orderMonth") != null && StringUtils.isNotBlank(paramsMap.get("orderMonth").toString()))
            {
                sql.append("and DATE_FORMAT(orderDate,'%m') = :orderMonth ");
            }
            sql.append("group by DATE_FORMAT(orderDate,'%Y-%m') ");
            sql.append("order by orderDate desc");
            
            logger.info("sql:{}", sql.toString());
            
            return super.queryForList(sql.toString(), paramsMap, sEcho, iDisplayStart, iDisplayLength);
            
        }
        catch (Exception e)
        {
            throw new MineException(MineErrorCode.ERROR_DB_EXECUTE_FAIL, e);
        }
    }
    
    @Override
    public MinePagination<Map<String, String>> queryChildRebate(Map<String, Object> paramsMap, int sEcho,
        int iDisplayStart, int iDisplayLength)
        throws MineException
    {
        try
        {
            logger.info(paramsMap.toString());
            
            StringBuilder sql = new StringBuilder();
            sql.append(
                "select DATE_FORMAT(a.orderDate, '%Y-%m') as orderDate, ifnull(sum(a.parentRebate), 0) as parentRebate, ");
            sql.append("count(a.id) as count, sum(a.isParentRebate) as isParentRebate, ");
            sql.append("(select username from " + UserVO.TABLE_NAME + " b where b.innerID = a.innerID) as username ");
            sql.append("from t_yyb_sys_order a ");
            sql.append("where 1 =1 ");
            if (paramsMap.get("parentInnerID") != null
                && StringUtils.isNotBlank(paramsMap.get("parentInnerID").toString()))
            {
                sql.append("and a.parentInnerID = :parentInnerID ");
            }
            if (paramsMap.get("orderYear") != null && StringUtils.isNotBlank(paramsMap.get("orderYear").toString()))
            {
                sql.append("and DATE_FORMAT(a.orderDate,'%Y') = :orderYear ");
            }
            if (paramsMap.get("orderMonth") != null && StringUtils.isNotBlank(paramsMap.get("orderMonth").toString()))
            {
                sql.append("and DATE_FORMAT(a.orderDate,'%m') = :orderMonth ");
            }
            sql.append("group by DATE_FORMAT(a.orderDate,'%Y-%m'), a.innerID ");
            sql.append("order by a.orderDate desc");
            
            logger.info("sql:{}", sql.toString());
            
            return super.queryForList(sql.toString(), paramsMap, sEcho, iDisplayStart, iDisplayLength);
            
        }
        catch (Exception e)
        {
            throw new MineException(MineErrorCode.ERROR_DB_EXECUTE_FAIL, e);
        }
    }
    
    @Override
    public MinePagination<Map<String, String>> queryAllRebate(Map<String, Object> paramsMap, int sEcho,
        int iDisplayStart, int iDisplayLength)
        throws MineException
    {
        try
        {
            logger.info(paramsMap.toString());
            
            StringBuilder sql = new StringBuilder();
            sql.append(
                "select b.username as username, b.innerID as innerID, b.payAccount as payAccount, b.name as name, DATE_FORMAT(a.orderDate,'%Y-%m') as orderDate, sum(a.rebate) as rebate, ");
            sql.append("count(a.id) as count, sum(a.isRebate) as isRebate ");
            sql.append("from " + UserVO.TABLE_NAME + " b ");
            sql.append("left join " + OrderVO.TABLE_NAME + " a ");
            sql.append("on a.innerID = b.innerID ");
            sql.append("where 1 =1 ");
            sql.append("and b.role = " + UserRoleEnum.USER_NORMAL_ROLE.getRole() + " ");
            if (paramsMap.get("username") != null && StringUtils.isNotBlank(paramsMap.get("username").toString()))
            {
                sql.append("and username = :username ");
            }
            if (paramsMap.get("orderYear") != null && StringUtils.isNotBlank(paramsMap.get("orderYear").toString()))
            {
                sql.append("and DATE_FORMAT(a.orderDate,'%Y') = :orderYear ");
            }
            if (paramsMap.get("orderMonth") != null && StringUtils.isNotBlank(paramsMap.get("orderMonth").toString()))
            {
                sql.append("and DATE_FORMAT(a.orderDate,'%m') = :orderMonth ");
            }
            sql.append("group by b.username, DATE_FORMAT(a.orderDate,'%Y-%m') ");
            sql.append("order by a.orderDate desc");
            
            logger.info("sql:{}", sql.toString());
            
            return super.queryForList(sql.toString(), paramsMap, sEcho, iDisplayStart, iDisplayLength);
            
        }
        catch (Exception e)
        {
            throw new MineException(MineErrorCode.ERROR_DB_EXECUTE_FAIL, e);
        }
    }
    
    @Override
    public MinePagination<Map<String, String>> queryAllChildRebate(Map<String, Object> paramsMap, int sEcho,
        int iDisplayStart, int iDisplayLength)
        throws MineException
    {
        try
        {
            logger.info(paramsMap.toString());
            
            StringBuilder sql = new StringBuilder();
            sql.append(
                "select DATE_FORMAT(a.orderDate, '%Y-%m') as orderDate, ifnull(sum(a.parentRebate), 0) as parentRebate, ");
            sql.append("count(a.id) as count, sum(a.isParentRebate) as isParentRebate, a.innerID as innerID, ");
            sql.append("(select username from " + UserVO.TABLE_NAME + " b where b.innerID = a.innerID) as username, ");
            sql.append("(select username from " + UserVO.TABLE_NAME
                + " c where c.innerID = a.parentInnerID) as parentUsername, ");
            sql.append("(select payAccount from t_yyb_sys_user d where d.innerID = a.parentInnerID) as payAccount ");
            sql.append("from t_yyb_sys_order a ");
            sql.append("left join t_yyb_sys_user e on a.parentInnerID = e.innerID ");
            sql.append("where 1 =1 ");
            sql.append("and a.parentInnerID is not null ");
            if (paramsMap.get("username") != null && StringUtils.isNotBlank(paramsMap.get("username").toString()))
            {
                sql.append("and e.username = :username ");
            }
            if (paramsMap.get("orderYear") != null && StringUtils.isNotBlank(paramsMap.get("orderYear").toString()))
            {
                sql.append("and DATE_FORMAT(a.orderDate,'%Y') = :orderYear ");
            }
            if (paramsMap.get("orderMonth") != null && StringUtils.isNotBlank(paramsMap.get("orderMonth").toString()))
            {
                sql.append("and DATE_FORMAT(a.orderDate,'%m') = :orderMonth ");
            }
            sql.append("group by DATE_FORMAT(a.orderDate,'%Y-%m'), a.innerID ");
            sql.append("order by a.orderDate desc");
            
            logger.info("sql:{}", sql.toString());
            
            return super.queryForList(sql.toString(), paramsMap, sEcho, iDisplayStart, iDisplayLength);
            
        }
        catch (Exception e)
        {
            throw new MineException(MineErrorCode.ERROR_DB_EXECUTE_FAIL, e);
        }
    }
    
    @Override
    public List<Map<String, Object>> queryAllRebate(Map<String, Object> paramsMap)
        throws MineException
    {
        try
        {
            logger.info(paramsMap.toString());
            
            StringBuilder sql = new StringBuilder();
            sql.append(
                "select b.username as username, b.innerID as innerID, b.payAccount as payAccount, b.name as name, DATE_FORMAT(a.orderDate,'%Y-%m') as orderDate, ifnull(sum(a.rebate), 0) as rebate, ");
            sql.append("count(a.id) as count, sum(a.isRebate) as isRebate ");
            sql.append("from " + UserVO.TABLE_NAME + " b ");
            sql.append("left join " + OrderVO.TABLE_NAME + " a ");
            sql.append("on a.innerID = b.innerID ");
            sql.append("where 1 =1 ");
            sql.append("and b.role = " + UserRoleEnum.USER_NORMAL_ROLE.getRole() + " ");
            if (paramsMap.get("username") != null && StringUtils.isNotBlank(paramsMap.get("username").toString()))
            {
                sql.append("and username = :username ");
            }
            if (paramsMap.get("orderYear") != null && StringUtils.isNotBlank(paramsMap.get("orderYear").toString()))
            {
                sql.append("and DATE_FORMAT(a.orderDate,'%Y') = :orderYear ");
            }
            if (paramsMap.get("orderMonth") != null && StringUtils.isNotBlank(paramsMap.get("orderMonth").toString()))
            {
                sql.append("and DATE_FORMAT(a.orderDate,'%m') = :orderMonth ");
            }
            sql.append("group by b.username, DATE_FORMAT(a.orderDate,'%Y-%m') ");
            sql.append("order by a.orderDate desc");
            
            logger.info("sql:{}", sql.toString());
            
            return super.queryForList(sql.toString(), paramsMap);
        }
        catch (Exception e)
        {
            throw new MineException(MineErrorCode.ERROR_DB_EXECUTE_FAIL, e);
        }
    }
    
    @Override
    public List<Map<String, Object>> queryAllChildRebate(Map<String, Object> paramsMap)
        throws MineException
    {
        try
        {
            logger.info(paramsMap.toString());
            
            StringBuilder sql = new StringBuilder();
            sql.append(
                "select DATE_FORMAT(a.orderDate, '%Y-%m') as orderDate, ifnull(sum(a.parentRebate), 0) as parentRebate, ");
            sql.append("count(a.id) as count, sum(a.isParentRebate) as isParentRebate, a.innerID as innerID, ");
            sql.append("(select username from " + UserVO.TABLE_NAME + " b where b.innerID = a.innerID) as username, ");
            sql.append("(select username from " + UserVO.TABLE_NAME
                + " c where c.innerID = a.parentInnerID) as parentUsername, ");
            sql.append("(select payAccount from t_yyb_sys_user d where d.innerID = a.parentInnerID) as payAccount ");
            sql.append("from t_yyb_sys_order a ");
            sql.append("where 1 =1 ");
            sql.append("and a.parentInnerID is not null ");
            if (paramsMap.get("username") != null && StringUtils.isNotBlank(paramsMap.get("username").toString()))
            {
                sql.append("and a.username = :username ");
            }
            if (paramsMap.get("orderYear") != null && StringUtils.isNotBlank(paramsMap.get("orderYear").toString()))
            {
                sql.append("and DATE_FORMAT(a.orderDate,'%Y') = :orderYear ");
            }
            if (paramsMap.get("orderMonth") != null && StringUtils.isNotBlank(paramsMap.get("orderMonth").toString()))
            {
                sql.append("and DATE_FORMAT(a.orderDate,'%m') = :orderMonth ");
            }
            sql.append("group by DATE_FORMAT(a.orderDate,'%Y-%m'), a.innerID ");
            sql.append("order by a.orderDate desc");
            
            logger.info("sql:{}", sql.toString());
            
            return super.queryForList(sql.toString(), paramsMap);
        }
        catch (Exception e)
        {
            throw new MineException(MineErrorCode.ERROR_DB_EXECUTE_FAIL, e);
        }
    }
    
    /**
     * <pre>
     * <一句话功能简述>
     * 
     * 
     * 
     * <功能详细描述>
     * </pre>
     *
     * @author 姓名 工号
     * @version [版本号, 2017年7月28日]
     * @param orderVO
     * @throws MineException
     * @see [类、类#方法、类#成员]
     */
    @Override
    public void deleteOrder(OrderVO orderVO)
        throws MineException
    {
        try
        {
            logger.info(orderVO.toString());
            
            StringBuilder sql = new StringBuilder();
            sql.append("delete from " + OrderVO.TABLE_NAME);
            sql.append(" where orderID = :orderID");
            
            super.update(sql.toString(), orderVO);
        }
        catch (Exception e)
        {
            throw new MineException(MineErrorCode.ERROR_DB_EXECUTE_FAIL, e);
        }
    }
    
}
